package com.lyq.bean.mySql;

import com.lyq.bean.Year;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Year_Sql {

    /**
     * 获取所有届数列表
     * @return 届数列表
     */
    public static List<Year> getAllYears() {
        List<Year> years = new ArrayList<>();
        String sql = "SELECT year_id, enrollment_year FROM academic_years ORDER BY year_id";
        try  {
            (new MysqlLinking()).getLink();
            Connection conn = MysqlLinking.conn;
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Year year = new Year();
                year.setId(rs.getInt("year_id"));
                year.setName(rs.getString("enrollment_year"));
                years.add(year);
                System.out.println(year);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return years;
    }

    /**
     * 根据ID获取届数
     * @param id 届数ID
     * @return 届数对象
     */
    public static Year getYearById(int id) {
        Year year = null;
        String sql = "SELECT year_id, enrollment_year FROM academic_years WHERE year_id = ?";

        try (Connection conn = MysqlLinking.conn;
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    year = new Year();
                    year.setId(rs.getInt("id"));
                    year.setName(rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return year;
    }

    /**
     * 根据届数ID获取届数名称
     * @param id 届数ID
     * @return 届数名称
     */
    public static String getYearNameById(int id) {
        String name = "";
        String sql = "SELECT  enrollment_year FROM academic_years WHERE year_id= ?";

        try (Connection conn = MysqlLinking.conn;
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    name = rs.getString("name");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return name;
    }
}
